"""
Case Type   : GSC功能模块
Case Name   : gsc打开，lsc阈值设置较小，并发进行分区表相关操作
Create At   : 2024/10/26
Owner       : @lonely-dance
Description :
    1.配置数据库参数，设置local_syscache_threshold为1MB
    2.重启数据库生效
    3.创建分区表
    4.循环进行alter分区表操作
    5.步骤4同时，多线程并发对分区表循环进行查询
    6.清理环境
Expect      :
    1.修改参数成功
    2.重启成功
    3.执行成功
    4.成功
    5.成功
    6.成功
History     :
"""
import os
import time
import unittest

from testcase.utils.ComThread import ComThread
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro

pri_sh = CommonSH('PrimaryDbUser')
node_num = pri_sh.get_node_num()


@unittest.skipIf(node_num < 3, '非1主2备环境不执行')
class GscTestCase(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.tb_name = 'tb_gsc0066'
        self.com = Common('PrimaryDbUser')
        self.constant = Constant()
        self.p_node = Node('PrimaryDbUser')
        self.init_para1 = None
        self.init_para2 = None

    def test_GSC(self):
        text = '----step1:配置数据库参数;----'
        self.log.info(text)
        status_result = self.pri_sh.get_db_cluster_status('status')
        self.assertTrue(status_result, '执行失败:' + text)
        text = '----查看初始配置值;----'
        self.log.info(text)
        self.init_para1 = self.com.show_param('enable_global_syscache')
        self.init_para2 = self.com.show_param('local_syscache_threshold')
        self.log.info(self.init_para1)

        result = self.pri_sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"enable_global_syscache=on")
        self.log.info(result)
        self.assertTrue(result, '执行失败:' + text)
        result = self.pri_sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"local_syscache_threshold=1MB")
        self.log.info(result)
        self.assertTrue(result, '执行失败:' + text)

        text = '----step2:重启数据库生效; expect:重启成功----'
        self.log.info(text)
        restart = self.pri_sh.restart_db_cluster()
        self.assertTrue(restart, '执行失败:' + text)

        text = f'----step3:创建ustore宽表; expect:执行成功----'
        self.log.info(text)
        cmd = f"""
            drop table if exists {self.tb_name};
            create table {self.tb_name}(jid int,jn int ,name int)partition by range (jid) subpartition by range(jn)
            (partition hr1 values less than(10000)(subpartition hr11 values less than(10000),
            subpartition hr12 values less than(30000)),partition hr2 values less than(30000)
            (subpartition hr21 values less than(10000),subpartition hr22 values less than(maxvalue)));
            select * from {self.tb_name};
            insert into {self.tb_name} select generate_series(1,20000),generate_series(1,20000), 
            generate_series(1,20000);
            select count(*) from {self.tb_name};
        """
        self.log.info(cmd)
        res = self.pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res, '执行失败:' + text)
        self.assertIn('INSERT 0 20000', res, '执行失败:' + text)

        text = '----step4:循环进行alter分区表操作; expect:成功----'
        self.log.info(text)
        cmd = f"drop table if exists {self.tb_name};" \
              f"create table {self.tb_name}(jid int,jn int ,name int)partition by range (jid) subpartition " \
              "by range(jn)(partition hr1 values less than(10000)(subpartition hr11 values less than(10000)," \
              "subpartition hr12 values less than(30000)),partition hr2 values less than(30000)" \
              "(subpartition hr21 values less than(10000),subpartition hr22 values less than(maxvalue)));" \
              f"select * from {self.tb_name};" \
              f"alter table {self.tb_name} add column bak text;" \
              f"alter table {self.tb_name} drop column bak;"
        thread_1 = ComThread(self.pri_sh.cycle_exec_sql, args=(cmd, 1000, False, None, True))
        thread_1.setDaemon(True)
        thread_1.start()

        text = '----step5:多线程并发对分区表循环进行查询; expect:成功----'
        self.log.info(text)
        cmd = f"select * from {self.tb_name}; "
        self.log.info(cmd)
        select_thread_num = 4
        connect_thread = []
        for i in range(select_thread_num):
            connect_thread.append(ComThread(self.pri_sh.cycle_exec_sql, args=(
                cmd, 120, False, None, True)))
            connect_thread[i].setDaemon(True)
            connect_thread[i].start()

        for i in range(select_thread_num):
            connect_thread[i].join(300)
            result = connect_thread[i].get_result()
            self.log.info(result)
            self.assertIn('jid | jn | name \n-----+----+------\n(0 rows)', result, '执行失败:' + text)

        thread_1.join(500)
        res = thread_1.get_result()
        self.log.info(res)
        self.assertNotIn('ERROR', res, '执行失败:' + text)

    def tearDown(self):
        text = '----step6:清理环境; expect:成功----'
        self.log.info(text)
        cmd = f"""
            drop table if exists {self.tb_name};
        """
        res = self.pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, '执行失败:' + text)

        text = '----恢复数据库参数----'
        self.log.info(text)
        result = self.pri_sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"enable_global_syscache={self.init_para1}")
        self.assertTrue(result, '执行失败:' + text)

        result = self.pri_sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"local_syscache_threshold={self.init_para2}")
        self.assertTrue(result, '执行失败:' + text)

        text = '----重启数据库----'
        self.log.info(text)
        restart = self.pri_sh.restart_db_cluster()
        self.assertTrue(restart, '执行失败:' + text)

        self.log.info(f'----{os.path.basename(__file__)} end----')
